SET IDENTITY_INSERT [changenotificationtype] ON
GO
INSERT INTO [changenotificationtype] ([id], [typename], [createdate], [createdby], [lastupdated], [lastupdatedby], [module], [type], [description]) VALUES ('11', 'The Financial Report was approved', '2012-12-11 00:00:00.000', '211', '2012-12-11 00:00:00.000', '211', 'Reporting Requirements', null, 'When the Financial Reports are approved.');
INSERT INTO [changenotificationtype] ([id], [typename], [createdate], [createdby], [lastupdated], [lastupdatedby], [module], [type], [description]) VALUES ('12', 'The IMP was approved', '2012-12-11 00:00:00.000', '211', '2012-12-11 00:00:00.000', '211', 'Partner Monitoring', null, 'When the IMPS are approved.');
INSERT INTO [changenotificationtype] ([id], [typename], [createdate], [createdby], [lastupdated], [lastupdatedby], [module], [type], [description]) VALUES ('13', 'The AASS of Interim Audit was approved', '2012-12-11 00:00:00.000', '211', '2012-12-11 00:00:00.000', '211', 'Partner Monitoring', null, 'When the AASS of Interim Audits are approved.');
INSERT INTO [changenotificationtype] ([id], [typename], [createdate], [createdby], [lastupdated], [lastupdatedby], [module], [type], [description]) VALUES ('14', 'The Interim Audit was approved', '2012-12-11 00:00:00.000', '211', '2012-12-11 00:00:00.000', '211', 'Partner Monitoring', null, 'When the Interim Audits are approved.');
INSERT INTO [changenotificationtype] ([id], [typename], [createdate], [createdby], [lastupdated], [lastupdatedby], [module], [type], [description]) VALUES ('15', 'The AASS of Final Audit was approved', '2012-12-11 00:00:00.000', '211', '2012-12-11 00:00:00.000', '211', 'Partner Monitoring', null, 'When the AASS of Final Audits are approved.');
INSERT INTO [changenotificationtype] ([id], [typename], [createdate], [createdby], [lastupdated], [lastupdatedby], [module], [type], [description]) VALUES ('16', 'The Final Audit was approved', '2012-12-11 00:00:00.000', '211', '2012-12-11 00:00:00.000', '211', 'Partner Monitoring', null, 'When the Final Audits are approved.');
GO
SET IDENTITY_INSERT [changenotificationtype] OFF
GO

INSERT INTO [restricteduielements] ([nameorvalue],[submenuorform]) VALUES ('The Financial Report was approved','Change Notifications');
INSERT INTO [restricteduielements] ([nameorvalue],[submenuorform]) VALUES ('The IMP was approved','Change Notifications');
INSERT INTO [restricteduielements] ([nameorvalue],[submenuorform]) VALUES ('The AASS of Interim Audit was approved','Change Notifications');
INSERT INTO [restricteduielements] ([nameorvalue],[submenuorform]) VALUES ('The Interim Audit was approved','Change Notifications');
INSERT INTO [restricteduielements] ([nameorvalue],[submenuorform]) VALUES ('The AASS of Final Audit was approved','Change Notifications');
INSERT INTO [restricteduielements] ([nameorvalue],[submenuorform]) VALUES ('The Final Audit was approved','Change Notifications');

INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (85,1);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (85,2);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (85,3);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (85,4);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (85,5);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (85,6);

INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (86,1);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (86,2);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (86,3);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (86,4);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (86,5);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (86,6);

INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (87,1);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (87,2);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (87,3);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (87,4);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (87,5);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (87,6);

INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (88,1);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (88,2);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (88,3);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (88,4);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (88,5);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (88,6);

INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (89,1);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (89,2);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (89,3);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (89,4);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (89,5);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (89,6);

INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (90,1);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (90,2);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (90,3);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (90,4);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (90,5);
INSERT INTO [restricteduielements_roleprivileges] ([uielementid],[roleid]) VALUES (90,6);

GO

DROP TRIGGER [dbo].[projectreportsUpdateApprovedByFinance];
DROP TRIGGER [dbo].[impInsertIMPApproved];
DROP TRIGGER [dbo].[auditsUpdateIAASSApproved];
DROP TRIGGER [dbo].[auditsUpdateIAApproved];
DROP TRIGGER [dbo].[auditsUpdateFAASSApproved];
DROP TRIGGER [dbo].[auditsUpdateFAApproved];
GO

CREATE trigger [dbo].[projectreportsUpdateApprovedByFinance] on [dbo].[projectreports] for update as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The Financial Report # <Financial Report ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i JOIN DELETED d ON i.id = d.id 
					WHERE i.approvedbyfinance IS NOT NULL AND i.approvedbyfinance='1' 
					AND (d.approvedbyfinance IS NULL OR i.approvedbyfinance<>d.approvedbyfinance))
	begin
		select @id=id,@projectid=projectid  
		from INSERTED;
		
		set @message='The Financial Report #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'showAddReportingrequirements.do?cipeprojectId='+cast(@projectid as varchar(20)),11,null,null,null,null);
	end

end;
GO

CREATE trigger [dbo].[impInsertIMPApproved] on [dbo].[imp] for insert as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The IMP # <IMP ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i
					WHERE i.impperformed IS NOT NULL)
	begin
		select @id=i.id,@projectid=project.id  
		from INSERTED i
		JOIN audits ON i.auditsid = audits.id
		JOIN project ON audits.projectid = project.id;
		
		set @message='The IMP #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'auditsForm.do?cipeprojectId='+cast(@projectid as varchar(20)),12,null,null,null,null);
	end

end;
GO

CREATE trigger [dbo].[auditsUpdateIAASSApproved] on [dbo].[audits] for update as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The AASS of Interim Audit # <Interim Audit ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i JOIN DELETED d ON i.id = d.id 
					WHERE i.iaselectionmemoapproved IS NOT NULL
					AND (d.iaselectionmemoapproved IS NULL OR i.iaselectionmemoapproved<>d.iaselectionmemoapproved))
	begin
		select @id=i.id,@projectid=project.id  
		from INSERTED i
		JOIN project ON i.projectid = project.id;
		
		set @message='The AASS of Interim Audit #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'auditsForm.do?cipeprojectId='+cast(@projectid as varchar(20)),13,null,null,null,null);
	end

end;
GO

CREATE trigger [dbo].[auditsUpdateIAApproved] on [dbo].[audits] for update as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The Interim Audit # <Interim Audit ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i JOIN DELETED d ON i.id = d.id 
					WHERE i.iaapproved IS NOT NULL 
					AND (d.iaapproved IS NULL OR i.iaapproved<>d.iaapproved))
	begin
		select @id=i.id,@projectid=project.id  
		from INSERTED i
		JOIN project ON i.projectid = project.id;
		
		set @message='The Interim Audit #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'auditsForm.do?cipeprojectId='+cast(@projectid as varchar(20)),14,null,null,null,null);
	end

end;
GO

CREATE trigger [dbo].[auditsUpdateFAASSApproved] on [dbo].[audits] for update as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The AASS of Final Audit # <Final Audit ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i JOIN DELETED d ON i.id = d.id 
					WHERE i.faselectionmemoapproved IS NOT NULL
					AND (d.faselectionmemoapproved IS NULL OR i.faselectionmemoapproved<>d.faselectionmemoapproved))
	begin
		select @id=i.id,@projectid=project.id  
		from INSERTED i
		JOIN project ON i.projectid = project.id;
		
		set @message='The AASS of Final Audit #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'auditsForm.do?cipeprojectId='+cast(@projectid as varchar(20)),15,null,null,null,null);
	end

end;
GO

CREATE trigger [dbo].[auditsUpdateFAApproved] on [dbo].[audits] for update as begin
--Increase change notifications to include when financial reports, IMPs and audits are approved
--A change notification should appear when: financial reports are approved; IMPS are approved; AASS are approved; and interim and financial audits are approved.
--The Final Audit # <Final Audit ID> of Project # <Project ID> was approved on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);

IF EXISTS (SELECT *FROM INSERTED i JOIN DELETED d ON i.id = d.id 
					WHERE i.faapproved IS NOT NULL 
					AND (d.faapproved IS NULL OR i.faapproved<>d.faapproved))
	begin
		select @id=i.id,@projectid=project.id  
		from INSERTED i
		JOIN project ON i.projectid = project.id;
		
		set @message='The Final Audit #'+CAST(@id as varchar(20))+' of Project #'+CAST(@projectid as varchar(100))+
		' was approved on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid,programofficer,regionaldirector,programassistant,regionid)
		values(@message,GETDATE(),'auditsForm.do?cipeprojectId='+cast(@projectid as varchar(20)),16,null,null,null,null);
	end

end;
GO